package com.sgl.gendatabasedocument;

import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@SpringBootApplication
public class GenDatabaseDocumentApplication implements CommandLineRunner {

    private final Environment env;
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public GenDatabaseDocumentApplication(Environment env, JdbcTemplate jdbcTemplate) {
        this.env = env;
        this.jdbcTemplate = jdbcTemplate;
    }

    public static void main(String[] args) {
        SpringApplication.run(GenDatabaseDocumentApplication.class, args);
    }

    @Override
    public void run(String... args) {
        List<Map<String,Object>> list = findDataBase();
        Map<String,List<List<String>>> sheets = new LinkedHashMap<>();
        String tbl = "";
        // 根据每列数据进行循环
        List<List<String>> rows = null;
        List<String> cols;
        assert list != null;
        for (Map<String, Object> record : list) {
            if (!tbl.equals(record.get("表名"))) {
                if (!"".equals(tbl)) {
                    sheets.put(tbl, rows);
                }
                tbl = record.get("表名").toString();
                rows = new ArrayList<>();
            }
            cols = new ArrayList<>();
            cols.add(toString(record.get("列名")));
            cols.add(toString(record.get("数据类型")));
            cols.add(toString(record.get("允许为空")));
            cols.add(toString(record.get("主键")));
            cols.add(toString(record.get("默认值")));
            cols.add(toString(record.get("备注")));
            assert rows != null;
            rows.add(cols);
        }
        sheets.put(tbl, rows);
        String fileName = env.getProperty("output.path") + env.getProperty("output.name") + ".xlsx";
        createExcel(sheets, fileName);
        System.out.println("Success! Generator Complete!");
    }

    private List<Map<String,Object>> findDataBase() {

        String databaseType = env.getProperty("spring.profiles.active");
        String sql;
        if("mysql".equals(databaseType)) {
            sql = "SELECT table_name 表名, COLUMN_NAME 列名, COLUMN_TYPE 数据类型, IS_NULLABLE 允许为空," +
                    " COLUMN_KEY 主键, COLUMN_DEFAULT 默认值, COLUMN_COMMENT 备注 FROM INFORMATION_SCHEMA.COLUMNS" +
                    " where table_schema = ? order by table_name";
            return jdbcTemplate.queryForList(sql,env.getProperty("output.name"));
        }else if("oracle".equals(databaseType)) {
            sql = "SELECT t.table_name as 表名, t.column_name as 列名," +
                    " t.DATA_TYPE || '(' || t.DATA_LENGTH || ')' as 数据类型," +
                    " t.NULLABLE as 允许为空, nvl2(con.POSITION,'Y','N') 主键," +
                    " t.DATA_DEFAULT as 默认值, t1.COMMENTS as 备注" +
                    " FROM User_Tab_Cols t " +
                    " inner join User_Col_Comments t1 on t.table_name = t1.table_name AND t.column_name = t1.column_name" +
                    " left join ( select cu.TABLE_NAME,cu.COLUMN_NAME,cu.POSITION from user_cons_columns cu, user_constraints au " +
                    "              where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' ) con " +
                    " on t.table_name=con.table_name and t.column_name = con.COlUMN_NAME order by t.table_name, t.column_id";
            return jdbcTemplate.queryForList(sql);
        }else {
            sql = "select\n" +
                    "       d.name as 表名,       --如果表名相同就返回空\n" +
                    "       a.name + '(' + cast(columnproperty(a.id, a.name, 'PRECISION') as varchar) + ')' as 列名,\n" +
                    "       b.name as 数据类型,\n" +
                    "       (case when a.isnullable = 1 then 'Y' else 'N' end) as 允许为空,\n" +
                    "       (case when (select count(*) from sys.sysobjects --查询主键\n" +
                    "       where (name in ( select name from sys.sysindexes where (id = a.id) and (indid in (select indid from sys.sysindexkeys where (id = a.id) and (colid in (select colid from sys.syscolumns where (id = a.id) and (name = a.name)) )) )) ) and (xtype = 'PK')) > 0 then 'Y' else 'N' end) as 主键,       --查询主键END\n" +
                    "       isnull(e.text, '') as 默认值,\n" +
                    "       isnull(convert(varchar(200),g.value), '') as 备注\n" +
                    "from sys.syscolumns a\n" +
                    "       left join sys.systypes b on a.xtype = b.xusertype\n" +
                    "       inner join sys.sysobjects d on a.id = d.id\n" +
                    "                                        and d.xtype = 'U'\n" +
                    "                                        and d.name <> 'dtproperties'\n" +
                    "       left join sys.syscomments e on a.cdefault = e.id\n" +
                    "       left join sys.extended_properties g on a.id = g.major_id\n" +
                    "                                                and a.colid = g.minor_id\n" +
                    "order by a.id, a.colorder;";
            return jdbcTemplate.queryForList(sql);
        }
    }

    private void createHistory(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setWrapText(true);
        Sheet sheetHistory = wb.createSheet("变更履历");
        sheetHistory.setColumnWidth(0, 8*256);
        sheetHistory.setColumnWidth(1, 12*256);
        sheetHistory.setColumnWidth(2, 12*256);
        sheetHistory.setColumnWidth(3, 25*256);
        sheetHistory.setColumnWidth(4, 15*256);
        sheetHistory.setColumnWidth(5, 40*256);
        sheetHistory.setColumnWidth(6, 40*256);
        for(int i=0;i<25;i++) {
            Row row = sheetHistory.createRow(i);
            Cell cell0 = row.createCell(0);
            Cell cell1 = row.createCell(1);
            Cell cell2 = row.createCell(2);
            Cell cell3 = row.createCell(3);
            Cell cell4 = row.createCell(4);
            Cell cell5 = row.createCell(5);
            Cell cell6 = row.createCell(6);
            cell0.setCellStyle(style);
            cell1.setCellStyle(style);
            cell2.setCellStyle(style);
            cell3.setCellStyle(style);
            cell4.setCellStyle(style);
            cell5.setCellStyle(style);
            cell6.setCellStyle(style);
            if(i==0) {
                cell0.setCellValue("序号");
                cell1.setCellValue("变更日期");
                cell2.setCellValue("变更人");
                cell3.setCellValue("数据表");
                cell4.setCellValue("变更类型");
                cell5.setCellValue("变更内容");
                cell6.setCellValue("变更原因");
            }else {
                cell0.setCellFormula("row()-1");
            }
        }
    }

    private void createExcel(Map<String, List<List<String>>> map, String filename) {
        Workbook wb = new XSSFWorkbook(); // or new XSSFWorkbook();
        createHistory(wb);
        // Title cells
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        CellStyle style1 = wb.createCellStyle();
        style1.setBorderBottom(BorderStyle.THIN);
        style1.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style1.setBorderLeft(BorderStyle.THIN);
        style1.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style1.setBorderRight(BorderStyle.THIN);
        style1.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style1.setBorderTop(BorderStyle.THIN);
        style1.setTopBorderColor(IndexedColors.BLACK.getIndex());
        Sheet sheetIndex = wb.createSheet("索引");
        List<String> tbls = new ArrayList<>();
        for (String key : map.keySet()) {
            Sheet sheet = wb.createSheet(key);
            tbls.add(key);
            Row row = sheet.createRow(0);
            Cell cell0 = row.createCell(0);
            cell0.setCellValue("编号");
            cell0.setCellStyle(style);
            Cell cell1 = row.createCell(1);
            cell1.setCellValue("列名");
            cell1.setCellStyle(style);
            Cell cell2 = row.createCell(2);
            cell2.setCellValue("数据类型");
            cell2.setCellStyle(style);
            Cell cell3 = row.createCell(3);
            cell3.setCellValue("允许为空");
            cell3.setCellStyle(style);
            Cell cell4 = row.createCell(4);
            cell4.setCellValue("主键");
            cell4.setCellStyle(style);
            Cell cell5 = row.createCell(5);
            cell5.setCellValue("默认值");
            cell5.setCellStyle(style);
            Cell cell6 = row.createCell(6);
            cell6.setCellValue("备注");
            cell6.setCellStyle(style);
            List<List<String>> list = map.get(key);
            for (int index = 0; index < list.size(); index++) {
                Row row1 = sheet.createRow(index + 1);
                for (int i = 0; i < 4; i++) {
                    Cell cell_0 = row1.createCell(0);
                    cell_0.setCellFormula("row()-1");
                    cell_0.setCellStyle(style1);
                    Cell cell_1 = row1.createCell(1);
                    cell_1.setCellValue(list.get(index).get(0));
                    cell_1.setCellStyle(style1);
                    Cell cell_2 = row1.createCell(2);
                    cell_2.setCellValue(list.get(index).get(1));
                    cell_2.setCellStyle(style1);
                    Cell cell_3 = row1.createCell(3);
                    cell_3.setCellValue(list.get(index).get(2));
                    cell_3.setCellStyle(style1);
                    Cell cell_4 = row1.createCell(4);
                    cell_4.setCellValue(list.get(index).get(3));
                    cell_4.setCellStyle(style1);
                    Cell cell_5 = row1.createCell(5);
                    cell_5.setCellValue(list.get(index).get(4));
                    cell_5.setCellStyle(style1);
                    Cell cell_6 = row1.createCell(6);
                    cell_6.setCellValue(list.get(index).get(5));
                    cell_6.setCellStyle(style1);
                }
            }
        }
        CreationHelper createHelper = wb.getCreationHelper();
        /* 设置为超链接的样式 */
        CellStyle linkStyle = wb.createCellStyle();
        Font cellFont = wb.createFont();
        cellFont.setUnderline((byte) 1);
        cellFont.setColor(IndexedColors.BLUE.getIndex());
        linkStyle.setFont(cellFont);
        for (int k = 0; k < tbls.size(); k++) {
            Row row = sheetIndex.createRow(k + 1);
            Cell linkCell = row.createCell(1);
            Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.DOCUMENT);
            // "#"表示本文档 "明细页面"表示sheet页名称 "A10"表示第几列第几行
            hyperlink.setAddress("#" + tbls.get(k) + "!A1");
            linkCell.setHyperlink(hyperlink);
            // 点击进行跳转
            linkCell.setCellValue(tbls.get(k));
            linkCell.setCellStyle(linkStyle);
        }
        try (OutputStream fileOut = new FileOutputStream(filename)) {
            wb.write(fileOut);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private String toString(Object obj) {
        if(obj==null) {
            return "";
        }else {
            return obj.toString();
        }
    }

}
